<?php
/**
 * WpConnectMySQL.php
 * 
 * This file contains the WpConnectMySQL class which provides database access functions for mysql
 * @author Robert Schmidt <robert@wildpack.de>
 * @version 1.0
 * @package wildpack
 * @subpackage dataaccess.wpconnect
 */

/**
 * The IWpConnect Interface to implement
 */
require_once(dirname(__FILE__).'/IWpConnect.php');

/**
 * The WpConnectBase class to extend
 */
require_once(dirname(__FILE__).'/WpConnectSql.php');

/**
 * A class to connect to MySql databases
 * 
 * The WpConnectMySQL class provides database access functions for mysql
 * 
 * Example:
 * <code>
 * $conn = new WpConnectMySQL(array(
 *    'location' => 'localhost/a_database',
 *    'user' => 'a_user',
 *    'password' => 'a_password')
 * );
 * 
 * //-- Select data --
 * $conn->select('*', 'table', 'WHERE id = 1);
 * $arr = $conn->getResult('assocs');
 * foreach ($arr as $index => $dataset) {
 *   foreach ($dataset as $field => $value) {
 *     print('Dataset: '.strval($index).', Field: '.$field.', Value: '.strval($value));
 *   }
 * }
 * 
 * //-- Insert data --
 * $chk = $conn->insert('table', array(
 *    'id' => 2,
 *    'field1' => 'fieldvalue', //A varchar field
 *    'field2' => 2, //An int field
 *    'field3' => 1.5 //A float field
 * ));
 * 
 * if ($chk === true) {
 *   print('Successfully inserted.');
 * }
 * </code>
 * 
 * @author Robert Schmidt <robert@wildpack.de>
 * @version 1.0
 * @package wildpack
 * @subpackage dataaccess.wpconnect
 */
class WpConnectMySQL extends WpConnectSql implements IWpConnect
{
	/**
   * The id of the last inserted dataset
   */
	private $varInsertId = null;
  
  /**
   * The template for the limit statement {start} and {limit} as placeholders
   */
	protected $LimitTemplate = 'LIMIT {start}, {limit}'; //Override the limit template for MySQL
	
	
	/**
	 * Restrieves a list of fields an necessary information
	 * @param object $rawresult The raw result from datasource
	 * @return An associative array with field names as keys containing an assoc array with the following information
	 * table (string), null (bool), pk (bool), unique (bool), index (bool), numeric (bool), type (string)
	 */
	private function getFieldInfo($rawresult, $asAssoc = true)
	{
		$arrReturn = array();
		
		mysql_field_seek ($rawresult, 0);
		while ($objField = mysql_fetch_field($rawresult))
		{
			$row = array(
				'table' => $objField->table,
				'name' => $objField->name,
				'null' => $objField->not_null === 1 ? false : true,
				'pk' => $objField->primary_key === 1 ? true : false,
				'unique' => $objField->unique_key === 1 ? true : false,
				'index' => $objField->multiple_key === 1 ? true : false,
				'numeric' => $objField->numeric === 1 ? true : false,
				'type' => $objField->type
			);
			
			if ($asAssoc === true) {
				$arrReturn[$objField->name] = $row;
			} else {
				$arrReturn[] = $row;
			}
		}
		return $arrReturn;
	}
	
	/**
	 * Converts the specific data type into a basic data type
	 * @param string $dataType The data type to convert or false
	 * @param mixed $value The value to convert
	 * @return The converted data type
	 */
	private function convertType($dataType, $value)
	{
		if (is_null($value)) {
			return null;
		}
		
		$arrFloat = array('float', 'double', 'real');
		$arrInt = array('int', 'long', 'integer', 'mediumint', 'smallint', 'bigint', 'tinyint');
		
		if (strpos($dataType, '(') !== false) {
			$dataType = substr($dataType, 0, strpos($dataType, '('));
		}
		
		if (in_array($dataType, $arrFloat, true) === true) {
			return floatval($value);
		}
		
		if (in_array($dataType, $arrInt, true) === true) {
			return intval($value);
		}
		return $value;
	}
	
	/**
   * Convert the rawresult to an array of objects (stdClass)
   * @return An array the selected data
   */
	protected function convertToObjects()
	{
		$arrReturn = array();
		$arrFields = array();
		$rawresult = $this->RawResult;
		$varTemp = null;
		
		//-- Check parameter --
		if (isset($rawresult) === false || is_null($rawresult) === true) {
			return null;
		}
		if (is_resource($rawresult) === false || get_resource_type($rawresult) !== 'mysql result') {
			return $rawresult;
		}
		if (mysql_num_rows($rawresult) === 0) {
			return $arrReturn;
		}
		
		$arrFields = $this->getFieldInfo($rawresult, true);
		
		//-- Go through result and convert --
		mysql_data_seek ($rawresult, 0);
		while ($row = mysql_fetch_assoc($rawresult)) {
			unset($varTemp);
			
			foreach ($row as $strField => $varValue) {
				$varTemp->$strField = $this->convertType($arrFields[$strField]['type'] ,$varValue);
			}
			$arrReturn[] = $varTemp;
		}
		
		return $arrReturn;
	}
	
	/**
   * Convert the rawresult to an array of arrays
   * @return An array the selected data
   */
	protected function convertToArrays()
	{
		$arrReturn = array();
		$arrFields = array();
		$rawresult = $this->RawResult;
		$varTemp = null;
		
		//-- Check parameter --
		if (isset($rawresult) === false || is_null($rawresult) === true) {
			return null;
		}
		if (is_resource($rawresult) === false || get_resource_type($rawresult) !== 'mysql result') {
			return $rawresult;
		}
		if (mysql_num_rows($rawresult) === 0) {
			return $arrReturn;
		}
		
		$arrFields = $this->getFieldInfo($rawresult, false);
		
		//-- Go through result and convert --
		mysql_data_seek ($rawresult, 0);
		while ($row = mysql_fetch_row($rawresult)) {
			unset($varTemp);
			$varTemp = array();
			$intFdC = 0;
			
			foreach ($row as $strField => $varValue) {
				$varTemp[$intFdC] = $this->convertType($arrFields[$intFdC]['type'] ,$varValue);
				$intFdC++;
			}
			$arrReturn[] = $varTemp;
		}
		
		return $arrReturn;
	}
	
	/**
	 * Returns an array of associative arrays (fieldnames as array keys) of the retrieved data 
	 * @return An array the selected data
	 */
	protected function convertToAssocs()
	{
		$arrReturn = array();
		$arrFields = array();
		$rawresult = $this->RawResult;
		$varTemp = null;
		
		//-- Check parameter --
		if (isset($rawresult) === false || is_null($rawresult) === true) {
			return null;
		}
		if (is_resource($rawresult) === false || get_resource_type($rawresult) !== 'mysql result') {
			return $rawresult;
		}
		if (mysql_num_rows($rawresult) === 0) {
			return $arrReturn;
		}
		
		$arrFields = $this->getFieldInfo($rawresult);
		
		//-- Go through result and convert --
		mysql_data_seek ($rawresult, 0);
		while ($row = mysql_fetch_assoc($rawresult)) {
			unset($varTemp);
			
			foreach ($row as $strField => $varValue) {
				$varTemp[$strField] = $this->convertType($arrFields[$strField]['type'] ,$varValue);
			}
			$arrReturn[] = $varTemp;
		}
		
		return $arrReturn;
	}
	
	/**
	 * Converts the raw data using a custom algorithm
	 * @param object $custom A custom class instance to use for converting
	 */
	protected function convertToCustom(&$custom)
	{
		$arrReturn = array();
    $arrFields = array();
    $rawresult = $this->RawResult;
    $varTemp = null;
    
    //-- Check parameter --
    if (isset($rawresult) === false || is_null($rawresult) === true) {
      return null;
    }
    if (is_resource($rawresult) === false || get_resource_type($rawresult) !== 'mysql result') {
      return $rawresult;
    }
    if (mysql_num_rows($rawresult) === 0) {
      return $arrReturn;
    }
    
    $arrFields = $this->getFieldInfo($rawresult);
    
    //-- Go through result and convert --
    mysql_data_seek ($rawresult, 0);
    while ($row = mysql_fetch_assoc($rawresult)) {
      foreach ($row as $strField => $varValue) {
         $custom->set($strField, $this->convertType($arrFields[$strField]['type'] ,$varValue));
      }
    }
	}
	
	/**
	 * Retrieves an associative array of connection parameters (location, host, user, password, database)
	 * @return The connection array
	 */
	protected function getConn()
	{
		$strLocation = $this->Location;
		return array(
			'location' => $strLocation,
			'host' => substr($strLocation, 0, strpos($strLocation, '/')),
			'user' => $this->User,
			'password' => $this->Password,
			'database' => substr($strLocation, strpos($strLocation, '/')+1)
		);
	}
	
	/**
   * The class constructor
   * @param object $config[optional] The configuration object
   * @cfg string location The location of the datasource to use
   * @cfg string user The user to use for datasource authentication
   * @cfg string password The password to use for authentication
   * @cfg object log A log object to log errors to (must implement the interface IWPLog).
   * If no log object given, the errors are logged to an array which can be retrieved by getLog
   */
	public function __construct($config = null)
	{
		parent::__construct($config);
	}
	
	/** 
   * Permanently deletes data from a table
   * @param string $table The table to delete data of.
   * @param string $condition The condition to use for matching the data to remove
   * @return bool True if successfully deleted, false else
   */
	public function delete($table, $condition, $sort='', $dir='', $limit=0, $start=0)
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
 		
		if ($this->fireEvent('preDelete', array($this, $table, $condition)) !== true) {
      return false;
    }
		
		//-- Multi request --
		if ($this->isMultiRequest() === true && strpos($this->strMrFlags, '[delete]') !== false) {
			$this->arrMr[] = array(
        'mrtype' => 'delete',
        'table' => $table,
        'condition' => $condition,
        'sort' => $sort,
        'dir' => $dir,
        'limit' => $limit,
        'start' => $start
      );
			return true;
		}
		
		$this->reset();
		$arrConn = $this->getConn();
		
		$connection = mysql_connect($arrConn['host'], $arrConn['user'], $arrConn['password']);
		if(!$connection){
			$this->writeToLog($loc, '', '', '', '20101', '', array($arrConn['host']));
			return false;
		}
		
		$dbhandle = mysql_select_db($arrConn['database'], $connection);
		if(!$dbhandle){
			$this->writeToLog($loc, '', '', '', '20102', '', array($arrConn['database']));
			return false;
		}
		
		$strSql = $this->getSqlQueryForDelete($table, $condition, $sort, $dir, $limit, $start);
		if($strSql === false){
			$this->writeToLog($loc, '', '', '', '90101', '', array('getSqlQueryForDelete'));
			return false;
		}
		
		$result = mysql_query($strSql);
		if ($result === false) {
			$this->writeToLog($loc, '', '', '', '20103', '', array(mysql_error(), $strSql));
			return false;
		}
		
		$this->Total = mysql_affected_rows();
		$this->RawResult = $result;
		@mysql_close($connection);
		$this->fireEvent('postDelete', array($this, $table, $condition, $this->Total));
		return true;
	}
	
	/**
   * Executes a sql statement. 
   * @param string $statement The statement to execute
   * @return mixed True if successfully executed, false else
   */
	public function exec($statement)
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
		$this->reset();
		$arrConn = $this->getConn();
		
		$connection = mysql_connect($arrConn['host'], $arrConn['user'], $arrConn['password']);
		if(!$connection){
			$this->writeToLog($loc, '', '', '', '20101', '', array($arrConn['host']));
			return false;
		}
		$dbhandle = mysql_select_db($arrConn['database'], $connection);
		if(!$dbhandle){
			$this->writeToLog($loc, '', '', '', '20102', '', array($arrConn['database']));
			return false;
		}
				
		$result = mysql_query($statement);
		if($result === false){
			$this->writeToLog($loc, '', '', '', '20103', '', array(mysql_error(), $statement));
			return false;
		}
		
		$this->RawResult = $result;
		mysql_close($connection);
		return true;
	}
	
	/** 
   * Retrieves the id of the last inserted dataset (for auto-increment only). This method 
   * may not be supported by all WpConnect implementations
   * @return var The id of the last inserted dataset
   */
	public function getInsertId()
	{
		return $this->varInsertId;
	}

	/** 
   * Retrieves the total number of datasets (not the number, limited by LIMIT)
   * @return int The number of datasets returned or affected by a query
   */
	public function getTotal()
	{
		$this->Total = mysql_result(mysql_query('SELECT FOUND_ROWS()'),0);
		return $this->Total;
	}
	
	/** 
   * Insert data to a table
   * @param string $table The table to insert data to.
   * @param array $insertion An associative array containing key/value pairs of the data to insert
   * @return bool True if successfully done, else false
   */
	public function insert($table, $insert)
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
 		
		if ($this->fireEvent('preInsert', array($this, $table, $insert)) !== true) {
		  return false;
		}
		
		//-- Multi request --
		if ($this->isMultiRequest() === true && strpos($this->strMrFlags, '[insert]') !== false) {
			$this->arrMr[] = array(
        'mrtype' => 'insert',
        'table' => $table,
        'insertion' => $insert
      );
			return true;
		}
		
		$this->reset();
		$arrConn = $this->getConn();
		
		$connection = mysql_connect($arrConn['host'], $arrConn['user'], $arrConn['password']);
		if(!$connection){
			$this->writeToLog($loc, '', '', '', '20101', '', array($arrConn['host']));
			return false;
		}
		
		$dbhandle = mysql_select_db($arrConn['database'], $connection);
		if(!$dbhandle){
			$this->writeToLog($loc, '', '', '', '20102', '', array($arrConn['database']));
			return false;
		}
		
		$strSql = $this->getSqlQueryForInsert($table, $insert);
		if($strSql === false){
			$this->writeToLog($loc, '', '', '', '90101', '', array('getSqlQueryForInsert'));
			return false;
		}
		
		$result = mysql_query($strSql);
		if($result === false){
			$this->writeToLog($loc, '', '', '', '20103', '', array(mysql_error(), $strSql));
			return false;
		}
		
		$this->Total = mysql_affected_rows();
		$this->RawResult = $result;
		$this->varInsertId = mysql_insert_id();
		@mysql_close($connection);
		$this->fireEvent('postInsert', array($this, $table, $insert));
		return true;
	}
	
	/**
   * Starts processing all requests collected before for a multi request
   * @return The number of requests processed or false on error
   * @see startMultiRequest()
   * @see stopMultiRequest()
   */
	public function processMultiRequest()
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
		
		if ($this->isMultiRequest() !== true) {
			return 0;
		}
		
		$arrConn = $this->getConn();
		
		//--- Connect to database ---
		$connection = mysql_connect($arrConn['host'], $arrConn['user'], $arrConn['password']);
		if(!$connection){
			$this->writeToLog($loc, '', '', '', '20101', '', array($arrConn['host']));
			return false;
		}
		$dbhandle = mysql_select_db($arrConn['database'], $connection);
		if(!$dbhandle){
			$this->writeToLog($loc, '', '', '', '20102', '', array($arrConn['database']));
			return false;
		}
		
		$arrRequests = $this->arrMr;
		
		//-- Prepare and validate queries --
		mysql_query('begin');
		foreach ($arrRequests as $objRequest) {
			
			switch ($objRequest['mrtype']) {
				case 'update' : $strQuery = $this->getSqlQueryForUpdate($objRequest['table'], $objRequest['insertion'], $objRequest['condition']); break;
				case 'insert' : $strQuery = $this->getSqlQueryForInsert($objRequest['table'], $objRequest['insertion']); break;
				case 'delete' : $strQuery = $this->getSqlQueryForDelete($objRequest['table'], $objRequest['condition']); break;
				default: $strQuery = 'n/a';
			}
			
			if ($strQuery === false) {
				$this->writeToLog($loc, 'Anonymous', 'Error preparing multi request', 
					'The multi request could not be prepared. Error in the following request: Type:'.$objRequest->MrType.
					'Table: '.$objRequest->Table, '99999');
				mysql_query('rollback');
				return false;
			}
			
			if ($strQuery === 'n/a') {
				$this->writeToLog($loc, 'Anonymous', 'Error preparing multi request', 
					'The multi request could not be prepared. Multi request type "'.$objRequest->MrType.'" is not supported for multi requests', '99999');
				mysql_query('rollback');
				return false;
			}
			if (!mysql_query($strQuery)) {
				$this->writeToLog($loc, 'Anonymous', 'Error processing multi request', 
					'MySQL returned an error on requesting: '.$strQuery, '99999');
				mysql_query('rollback');
				return false;
			}
		}
		$ret = mysql_query('commit');
		if ($ret === false) {
			$this->writeToLog($loc, 'Anonymous', 'Error committing transaction', 
				'MySQL returned an error on commit: '.mysql_error(), '99999');
			return false;
		}
		
		return count($arrRequests);
	}
	
	/**
	 * Reset the connector for a new query
	 */
	public function reset()
	{
		$this->varInsertId = null;
		if (is_resource($this->RawResult) === true && get_resource_type($this->RawResult) === 'mysql result') {
			mysql_free_result($this->RawResult);
		}
		
		parent::reset();
	}
	
	/** 
   * Selects data from a table by using a condition to match
   * @param string $fields The fields to retrieve from table as comma separated string or * for all fields
   * @param string $table The table to select data from.
   * @param string $condition The condition to use for data selection
   * @param string $sort[optional] The column(s) to sort the data by (default "")
   * @param string $dir[optional] The sort direction (ASC or DESC) (default "ASC")
   * @param int $limit[optional] The number of datasets to return
   * @param int $start[optional] The start dataset of the datasets to return
   * @return bool True if successfully done, false else
   */
	public function select($fields, $table, $condition, $limit=0, $start=0, $sort='', $dir='ASC')
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
		$this->reset();
		$arrConn = $this->getConn();
		
    if ($this->fireEvent('preSelect', array($this, $table, $fields, $condition)) !== true) {
      return false;
    }
    
		$connection = mysql_connect($arrConn['host'], $arrConn['user'], $arrConn['password']);
		if(!$connection){
			$this->writeToLog($loc, '', '', '', '20101', '', array($arrConn['host']));
			return false;
		}
		$dbhandle = mysql_select_db($arrConn['database'], $connection);
		if(!$dbhandle){
			$this->writeToLog($loc, '', '', '', '20102', '', array($arrConn['database']));
			return false;
		}
				
		$strSql = $this->getSqlQueryForSelect($fields, $table, $condition, $sort, $dir, $limit, $start);
		if($strSql === false){
			$this->writeToLog($loc, '', '', '', '90101', '', array('getSqlQueryForSelect'));
			return false;
		}
		
		$result = mysql_query($strSql);
		if($result === false){
			$this->writeToLog($loc, '', '', '', '20103', '', array(mysql_error(), $strSql));
			return false;
		}
		
		$this->RawResult = $result;
		mysql_close($connection);
    $this->fireEvent('postSelect', array($this, $table, $fields, $condition));
		return true;
	}
	
	/** 
   * Updates data of a table
   * @param string $table The table to update data of.
   * @param array $insertion An associative array containing key/value pairs of the data to update
   * If a field is missing, its value will not be updated but kept in table
   * @param string $condition The condition to use to find the data to update
   * @return bool True if succesfully updated, false else
   */
	public function update($table, $update, $condition, $limit=0, $start=0, $sort='', $dir='ASC')
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
 		
    if ($this->fireEvent('preUpdate', array($this, $table, $condition, $update)) !== true) {
      return false;
    }
    
		//-- Multi request --
		if ($this->isMultiRequest() === true && strpos($this->strMrFlags, '[update]') !== false) {
			$this->arrMr[] = array(
        'mrtype' => 'update',
        'table' => $table,
        'insertion' => $update,
        'condition' => $condition
      );
			return true;
		}
		
		$this->reset();
		$arrConn = $this->getConn();
		
		$connection = mysql_connect($arrConn['host'], $arrConn['user'], $arrConn['password']);
		if(!$connection){
			$this->writeToLog($loc, '', '', '', '20101', '', array($arrConn['host']));
			return false;
		}
		$dbhandle = mysql_select_db($arrConn['database'], $connection);
		if(!$dbhandle){
			$this->writeToLog($loc, '', '', '', '20102', '', array($arrConn['database']));
			return false;
		}
		
		$strSql = $this->getSqlQueryForUpdate($table, $update, $condition, $sort, $dir, $limit, $start);
		if($strSql === false){
			$this->writeToLog($loc, '', '', '', '90101', '', array('getSqlQueryForUpdate'));
			return false;
		}
		
		$result = mysql_query($strSql);
		if($result === false){
			$this->writeToLog($loc, '', '', '', '20103', '', array(mysql_error(), $strSql));
			return false;
		}
		
		$this->Total = mysql_affected_rows();
		$this->RawResult = $result;
		mysql_close($connection);
    $this->fireEvent('postUpdate', array($this, $table, $condition, $update, $this->Total));
		return true;
	}
}
/* End of file WpConnectMySQL.php */
/* Location: ./wildpack/dataaccess/wpconnect/WpConnectMySQL.php */ 
